package ImplementInterface;

import java.io.DataOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.message.source.SmsVoicePromptSender;
import com.message.source.SmsVoicePromptSenderResult;
import com.qcloud.sms.SmsMultiSender;
import com.qcloud.sms.SmsMultiSenderResult;
import com.qcloud.sms.SmsSingleSender;
import com.qcloud.sms.SmsSingleSenderResult;
import com.message.source.*;
import DatabaseInterface.AndroidInterface;
import HibernateFacet.DataRecord;
import HibernateFacet.DeviceInformation;
import HibernateFacet.InstallationManagement;
import HibernateFacet.UserInformation;
import HibernateFacet.Warning;


public class ImplementAndroidInterface implements AndroidInterface{
	public SessionFactory sessionFactory;
	public Session session;
	public  List list =new ArrayList<Object>();
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@Override
	public int LoginVerified(String name, String password) {
		
		//for(int i=0; i<3000; i++) { System.out.println("repeat executing:"+i);
		try {
		 session=sessionFactory.openSession();
		 list = session.createSQLQuery("select *from  UserInformation where name='"+name+"' and password='"+password+"'"  ).addEntity(UserInformation.class).list();
		}catch (Exception e) {
			// TODO: handle exception
		}finally {
			try {
		 session.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
	//	}
		if(list.size()==1) {
			return ((UserInformation)list.get(0)).getId();
		}else return -1;
		  
		 
	}

	@Override
	public List GetCostList(int devID,String str) {
		List list=new ArrayList<DataRecord>();
		try {
			session=sessionFactory.openSession();   
			list=session.createSQLQuery("select *from DataRecord where DeviceInformation_id ='"+devID+"' "
			+ "AND  DATE_FORMAT( DateTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) ").addEntity(DataRecord.class).list();    
			if(list.size()==0) {
			list=session.createSQLQuery("select *from DataRecord where DeviceInformation_id in (SELECT id  FROM DeviceInformation where SeriesNumber in (select SeriesNumber from "
			+ "DeviceInformation group by SeriesNumber having count(*)>1 and SeriesNumber = '"+str+"' )) AND  DATE_FORMAT( DateTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) ").addEntity(DataRecord.class).list();    		
			}
		}catch (Exception e) {e.printStackTrace();}finally { session.close();}
		return list;
	}
	@Override
	public int getDeviceOnlineStatus(int devID,String str) {
		List list=new ArrayList<DataRecord>();
		int i=0;
		try {
			session=sessionFactory.openSession();   
			list=session.createSQLQuery("select *from DataRecord where DeviceInformation_id ='"+devID+"' "
			+ "order by id desc limit 1 ").addEntity(DataRecord.class).list(); 
			if(list.size()>0) {
				
				String dateTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.format(((DataRecord) list.get(0)).getDateTime());
				Date now = new Date();
				String nowTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);
				String year = dateTime.substring(2, 4);
				String month = dateTime.substring(5, 7);
				String day = dateTime.substring(8, 10);
				String hour = dateTime.substring(11, 13);
				String minute = dateTime.substring(14, 16);				
				long dateTime_1 = Long.parseLong(year + month + day + hour + minute);
				year = nowTime.substring(2, 4);
				month = nowTime.substring(5, 7);
				day = nowTime.substring(8, 10);
				hour = nowTime.substring(11, 13);
				minute = nowTime.substring(14, 16);			
				long nowTime_1 = Long.parseLong(year + month + day + hour + minute);

				if ((nowTime_1 - dateTime_1) > 5) {
					i= 0;
				}else {i= 1;}
//				System.out.println("间隔"+(nowTime_1 - dateTime_1)+"分钟");
			}
			else if(list.size()==0) {
				
			list=session.createSQLQuery("select *from DataRecord where DeviceInformation_id in (SELECT id  FROM DeviceInformation where SeriesNumber in (select SeriesNumber from "
			+ "DeviceInformation group by SeriesNumber having count(*)>1 and SeriesNumber = '"+str+"' )) order by id desc limit 1 ").addEntity(DataRecord.class).list();    		
			if(list.size()>0) {
				
				String dateTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.format(((DataRecord) list.get(0)).getDateTime());
				Date now = new Date();
				String nowTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);
				String year = dateTime.substring(2, 4);
				String month = dateTime.substring(5, 7);
				String day = dateTime.substring(8, 10);
				String hour = dateTime.substring(11, 13);
				String minute = dateTime.substring(14, 16);		
				long dateTime_1 = Long.parseLong(year + month + day + hour + minute);
				year = nowTime.substring(2, 4);
				month = nowTime.substring(5, 7);
				day = nowTime.substring(8, 10);
				hour = nowTime.substring(11, 13);
				minute = nowTime.substring(14, 16);			
				long nowTime_1 = Long.parseLong(year + month + day + hour + minute);

				if ((nowTime_1 - dateTime_1) > 5) {
					i= 0;
				}else {i= 1;}
//				System.out.println("间隔"+(nowTime_1 - dateTime_1)+"分钟");
			}	
			}//else if end
		}catch (Exception e) {e.printStackTrace();}finally { session.close();}
		return i;
	}
	@Override
	public List GetWarningList(int devID ,String str,String startdate, String stopdate) {
		stopdate = stopdate + " " + "23:59:59";
		List list=new ArrayList<Warning>();
//		System.out.println("------------getWarn");
			//list=session.createSQLQuery("select *from Warning where DeviceInformation_id in (select id from DeviceInformation where InstallationManagement_id in (select id from InstallationManagement where User_id in (select id from UserInformation where id='"+UserID+"'))) ").addEntity(Warning.class).list(); 
			try {
				session=sessionFactory.openSession();   
				list=session.createSQLQuery("select *from Warning where DeviceInformation_id ='"+devID+"'and DateTime  between  '" + startdate + "' and  '" + stopdate + "' order by id desc ").addEntity(Warning.class).list();    
				if(list.size()==0) {
				list=session.createSQLQuery("select *from Warning where DeviceInformation_id in (SELECT id  FROM DeviceInformation where SeriesNumber in (select SeriesNumber from "
				+ "DeviceInformation group by SeriesNumber having count(*)>1 and SeriesNumber = '"+str+"' )) and DateTime  between  '" + startdate + "' and  '" + stopdate + "' order by id desc ").addEntity(Warning.class).list();    		
				}
			}catch (Exception e) {
			e.printStackTrace();
			}finally {
				try {
			        session.close();
				}catch (Exception e) {	
					e.printStackTrace();
				}
			}
		    
		return list;
	}

	@Override
	public String getSeries(int devID) {
		try {
			session=sessionFactory.openSession();
		    list=session.createSQLQuery("select *from DeviceInformation where id='"+devID+"'").addEntity(DeviceInformation.class).list();
		}catch (Exception e) {
			e.printStackTrace();
			}finally {
				try {
			  session.close();
				}catch (Exception e) {	
					e.printStackTrace();
				}
			}
		if(list.size()>0) return ((DeviceInformation)list.get(0)).getSeriesNumber();
		else
		return null;
	}

	@Override
	public List GetDevList(int UserID) {
	
		List list=new ArrayList<DeviceInformation>();
		try {
			session=sessionFactory.openSession();
		//	list= session.createSQLQuery("call getCostList(1)").addEntity(DataRecord.class).list();
		//  list=session.createSQLQuery("select *from DataRecord where DeviceInformation_id in (select id from DeviceInformation where InstallationManagement_id in (select id from InstallationManagement where User_id in (select id from UserInformation where id='"+UserID+"'))) ").addEntity(DataRecord.class).list(); 	  
		list=session.createSQLQuery("select *from DeviceInformation where InstallationManagement_id in (select id from InstallationManagement where User_id in (select id from UserInformation where id='"+UserID+"'))").addEntity(DeviceInformation.class).list();
	
		}catch (Exception e) {
			e.printStackTrace();
			}finally {
				try {
			  session.close();
				}catch (Exception e) {	
					e.printStackTrace();
				}
			}
		return list;
	}

	@Override
	public int addDataRecord(int devID, int cost,int currentA,int voltageA,String status) {
		 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		  String date=sdf.format(new Date());
		  Date now = new Date();
			String dateTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);
			String dateTime_ago = new java.text.SimpleDateFormat("yyyy-MM-dd").format(now) + " " + "00:00:00";
//			System.out.println(devID+" "+cost+" "+currentA+" "+voltageA+" "+status);
		  int i=-1;
		  try {
				session=sessionFactory.openSession();
					List list_adddata=session.createSQLQuery("select *from DataRecord where DeviceInformation_id='"+devID+"'and DateTime  between  '" + dateTime_ago + "' and  '" + dateTime + "'").addEntity(DataRecord.class).list();
				if(list_adddata.size()==0) {
					i=session.createSQLQuery("insert into DataRecord (DeviceInformation_id,CurrentA, VoltageA,Cost,Status, DateTime) values ('"+devID+"','"+currentA+"','"+voltageA+"','"+cost+"','"+status+"','"+date+"')").executeUpdate();					
				}
				else if(list_adddata.size()>0){
					double cost_old=((DataRecord) list_adddata.get(0)).getCost();
						cost=cost+(int)cost_old;				
					i= session.createSQLQuery("update DataRecord set CurrentA='"+currentA+"', VoltageA='"+voltageA+"',"
							+ "Cost='"+cost+"',Status='"+status+"',DateTime='"+date+"' where DeviceInformation_id='"+devID+"'and DateTime  between  '" + dateTime_ago + "' and  '" + dateTime + "'" ).executeUpdate();					
				}
				
				}catch (Exception e) {e.printStackTrace();}finally {session.close();}
		  
		return i;
	}
	
	@Override
	public int add_UDP_DataRecord(int devID, int cost,int currentA,int voltageA,String status) {
		 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		  String date=sdf.format(new Date());
		  Date now = new Date();
			String dateTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);
			String dateTime_ago = new java.text.SimpleDateFormat("yyyy-MM-dd").format(now) + " " + "00:00:00";
			double cost_udp=(double)cost/6000d;
//			System.out.println(devID+" "+cost_udp+" "+currentA+" "+voltageA+" "+status);
		  int i=-1;
		  try {
				session=sessionFactory.openSession();
					List list_adddata=session.createSQLQuery("select *from DataRecord where DeviceInformation_id='"+devID+"'and DateTime  between  '" + dateTime_ago + "' and  '" + dateTime + "'").addEntity(DataRecord.class).list();
				if(list_adddata.size()==0) {
					i=session.createSQLQuery("insert into DataRecord (DeviceInformation_id,CurrentA, VoltageA,Cost,Status, DateTime) values ('"+devID+"','"+currentA+"','"+voltageA+"','"+cost_udp+"','"+status+"','"+date+"')").executeUpdate();					
				}
				else if(list_adddata.size()>0){
					double cost_old=((DataRecord) list_adddata.get(0)).getCost();
//					System.out.println("cost—old"+cost_old);
					cost_udp=cost_udp+cost_old;
//					System.out.println("cost_udp"+cost_udp);
					i= session.createSQLQuery("update DataRecord set CurrentA='"+currentA+"', VoltageA='"+voltageA+"',"
							+ "Cost='"+cost_udp+"',Status='"+status+"',DateTime='"+date+"' where DeviceInformation_id='"+devID+"'and DateTime  between  '" + dateTime_ago + "' and  '" + dateTime + "'" ).executeUpdate();					
				}
				
				}catch (Exception e) {e.printStackTrace();}finally {session.close();}
		  
		return i;
	}
	
	public void changeStatus(int devID,String status ) {
		 Date now = new Date();
		 String dateTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);
		 String dateTime_ago = new java.text.SimpleDateFormat("yyyy-MM-dd").format(now) + " " + "00:00:00";
		  try {
				session=sessionFactory.openSession();
					List list_adddata=session.createSQLQuery("select *from DataRecord where DeviceInformation_id='"+devID+"'and DateTime  between  '" + dateTime_ago + "' and  '" + dateTime + "'").addEntity(DataRecord.class).list();
			 if(list_adddata.size()>0){				
					 session.createSQLQuery("update DataRecord set Status='"+status+"',DateTime='"+dateTime+"' where DeviceInformation_id='"+devID+"'and DateTime  between  '" + dateTime_ago + "' and  '" + dateTime + "'" ).executeUpdate();					
				}			
				}catch (Exception e) {e.printStackTrace();}finally {session.close();}
	}

	@Override
	public int addWarning(int devID,String type,String series) {
		 
		 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		  String date=sdf.format(new Date());
		  int i=-1;
		  try {
				session=sessionFactory.openSession();
				i=session.createSQLQuery("insert into Warning (DeviceInformation_id, Type, DateTime) values ('"+devID+"','"+type+"','"+date+"')").executeUpdate();
				warningAlarm(devID,type,series);
				}catch (Exception e) {
					e.printStackTrace();
					}finally {
						try {
					    session.close();
						}catch (Exception e) {	
							e.printStackTrace();
						}
					}
		 
				
		  
		return i;
	}
	
	public void warningAlarm(int devID,String type,String series) throws Exception {
		int appid = 1400036402;
		String appkey = "99407e02cf8515dcbc8a142b15d834f1"; 
		String phonenumber="";
		
		List list=session.createSQLQuery("select *from DeviceInformation where SeriesNumber='"+series+"'").addEntity(DeviceInformation.class).list();
	
		
		for (int i = 0; i < list.size(); i++) {
			DeviceInformation jd = (DeviceInformation) list.get(i);
		    int InstallationManagement_id=jd.getInstallationManagement().getId();
		   
		    int user_id = ((Number) session.createSQLQuery("select User_id from InstallationManagement where id='"+InstallationManagement_id+"' ").uniqueResult()).intValue();
		    phonenumber=(String) ( session.createSQLQuery("select PhoneNumber from UserInformation where id='"+user_id+"' ").uniqueResult());
		   
		    SmsMultiSender multiSender = null;
			try {
				multiSender = new SmsMultiSender(appid, appkey);
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			SmsMultiSenderResult multiSenderResult;
			ArrayList<String> phoneNumbers = new ArrayList<String>();
			phoneNumbers.add(phonenumber);
			ArrayList<String> params = new ArrayList<String>();
			int tmplId = 1278976;
			params.add(series);
//			System.out.println(type);
			if(type.equals("1")) {
				params.add("漏电故障");
			}else if(type.equals("2")) {
				params.add("故障电弧故障");
			}
			
			try {
				multiSenderResult = multiSender.sendWithParam("86", phoneNumbers, tmplId, params, "", "", "");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			String type_str="";
			if(type.equals("1")) {
				type_str="漏电故障";
			}else if(type.equals("2")) {
				type_str="故障电弧故障";
			}
			String message = "尊敬的用户您好！您的"+series+"号低压智能断路器已检测出线路中存在"+type_str+"，请检修！";
			SmsVoicePromptSender smsVoicePromtSender = new SmsVoicePromptSender(appid, appkey);
			SmsVoicePromptSenderResult smsSingleVoiceSenderResult;
			try {
				smsSingleVoiceSenderResult = smsVoicePromtSender.send("86", phonenumber, 2, 2, message, "");
				System.out.println(smsSingleVoiceSenderResult);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
				   
		}
	
	@Override
	public List GetDevList(String series) {
		List list=new ArrayList<DeviceInformation>();
		try {
		session=sessionFactory.openSession();
		list=session.createSQLQuery("select *from DeviceInformation where SeriesNumber='"+series+"'").addEntity(DeviceInformation.class).list();
		
		}catch (Exception e) {
			e.printStackTrace();
			}finally {
				try {
			  session.close();
				}catch (Exception e) {	
					e.printStackTrace();
				}
			}
		return list;
	}
	
	public String UpdateUserInformation(String register_name,String register_password,String phone_number,String verify_number) {
		String str="";
		
		try {
			 session=sessionFactory.openSession();
			 list = session.createSQLQuery("select *from  UserInformation where Name='"+register_name+"'"  ).addEntity(UserInformation.class).list();
			}catch (Exception e) {
				// TODO: handle exception
			}finally {
				try {
			 session.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		if(list.size()>0) {
			return "exist";
		}
		
		try {
			 session=sessionFactory.openSession();
			 list = session.createSQLQuery("select *from  UserInformation where PhoneNumber='"+phone_number+"'"  ).addEntity(UserInformation.class).list();
			}catch (Exception e) {
				// TODO: handle exception
			}finally {
				try {
			 session.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		   if(list.size()>0) {
			   try {
				     str="update";
					 session=sessionFactory.openSession();
					 session.createSQLQuery("update UserInformation set Name='"+register_name+"', Password='"+register_password+"' where PhoneNumber='"+phone_number+"'"  ).executeUpdate();
					}catch (Exception e) {
						// TODO: handle exception
					}finally {
						try {
					 session.close();
						}catch (Exception e) {
							e.printStackTrace();
						}
					}
		   }else {
			   try {
				     str="insert";
					 session=sessionFactory.openSession();
					 session.createSQLQuery("insert into UserInformation (Name,Password,Privilege,PhoneNumber) value ('"+register_name+"','"+register_password+"',0,'"+phone_number+"')").executeUpdate();
				     List list= session.createSQLQuery("select *from  UserInformation where Password='"+register_password+"' and Name='"+register_name+"'").addEntity(UserInformation.class).list();
			         int id=0;
				     if(list.size()>0) {
			        	id=((UserInformation)list.get(0)).getId();
			        	session.createSQLQuery("insert into InstallationManagement(User_id,Area) value ( '"+id+"',0)").executeUpdate();
			         }
			   
			   }catch (Exception e) {
						// TODO: handle exception
					}finally {
						try {
					 session.close();
						}catch (Exception e) {
							e.printStackTrace();
						}
					}
		   }
		
		return str;
	}

	public String AddDevice(String register_name, String register_password, String series) {
        
	//	System.out.println(register_name+"  "+register_password);
		String str="";
		try {
			 session=sessionFactory.openSession();
			 list = session.createSQLQuery("select *from  DeviceInformation where InstallationManagement_id = (select id from InstallationManagement where User_id = (select id from UserInformation where name='"+register_name+"' and password='"+register_password+"')) and SeriesNumber='"+series+"'").addEntity(DeviceInformation.class).list();
			}catch (Exception e) {
				// TODO: handle exception
			}finally {
				try {
			 session.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		if(list.size()>0) {
			return "exist";
		}else {
			try {
				 session=sessionFactory.openSession();
				 list = session.createSQLQuery("select *from InstallationManagement where User_id = (select id from UserInformation where name='"+register_name+"' and password='"+register_password+"')").addEntity(InstallationManagement.class).list();
				}catch (Exception e) {
					// TODO: handle exception
				}finally {
					try {
				 session.close();
					}catch (Exception e) {
						e.printStackTrace();
					}
				}
			   if(list.size()>0) {
				   int id=((InstallationManagement)list.get(0)).getId();
				   try {
					     str="insert";
						 session=sessionFactory.openSession();
						 session.createSQLQuery("insert into DeviceInformation (InstallationManagement_id,SeriesNumber,PositionX,PositionY) value ('"+id+"','"+series+"',0,0)").executeUpdate();
						}catch (Exception e) {
							// TODO: handle exception
						}finally {
							try {
						 session.close();
							}catch (Exception e) {
								e.printStackTrace();
							}
						}
			   }else str="fail";
			
		}
			
			
		
		
		return str;
	}
	
	public List getControlPassword(String series) {
		List jsonList = new ArrayList<Map>();
		Map map = new HashMap<String, Object>();
		List list=new ArrayList<DeviceInformation>();
		Session session=sessionFactory.openSession();
		try {
	   
		list=session.createSQLQuery("select *from DeviceInformation where SeriesNumber='"+series+"'limit 1").addEntity(DeviceInformation.class).list();
		
		if(list.size()>0) {
		String control_password=((DeviceInformation)list.get(0)).getControlPassword();
		map.put("control_password", control_password);
		jsonList.add(map);
		}
		
		}catch (Exception e) {
			e.printStackTrace();
			}finally {session.close();}
		return jsonList;
	}
	
	public List verifyControlPassword(String series,String password) {
		List jsonList = new ArrayList<Map>();
		Map map = new HashMap<String, Object>();
		List list=new ArrayList<DeviceInformation>();
		Session session=sessionFactory.openSession();
		try {
	 
		list=session.createSQLQuery("select *from DeviceInformation where SeriesNumber='"+series+"'limit 1").addEntity(DeviceInformation.class).list();
		if(list.size()>0) {
		String control_password=((DeviceInformation)list.get(0)).getControlPassword();
		if(password.equals(control_password)) {
			map.put("result", "success");
		}else {
			map.put("result", "fail");
		}
		jsonList.add(map);
		}
		
		}catch (Exception e) {
			e.printStackTrace();
			}finally {session.close();}
		return jsonList;
	}
	
	public List updateControlPassword(String series,String new_password) {
		List jsonList = new ArrayList<Map>();
		Map map = new HashMap<String, Object>();
		List list=new ArrayList<DeviceInformation>();
		int i=-1;
		Session session=sessionFactory.openSession();
		try {
	    
		list=session.createSQLQuery("select *from DeviceInformation where SeriesNumber='"+series+"'limit 1").addEntity(DeviceInformation.class).list();
		if(list.size()>0) {
		i=session.createSQLQuery("update DeviceInformation set control_password='"+ new_password+"' where SeriesNumber='"+series+"'" ).executeUpdate();
		if(i>-1) {
			map.put("result", "change_success");
		}else {
			map.put("result", "change_fail");
		}
		
		jsonList.add(map);
		}
		
		}catch (Exception e) {
			e.printStackTrace();
			}finally {session.close();}
		return jsonList;
	}
	
}//end end
